﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Cosmetic.DataAccess;
using Cosmetic.DataEntity;
using System.Configuration;
using System.Data.Linq.SqlClient;
using System.Data.SqlClient;
using System.Data;

namespace Cosmetic.Logic
{
    /// <summary>
    /// 
    /// </summary>
    public class AddressManager : LogicBase
    {
        /// <summary>
        /// 向地址表里插入一行信息。这里Mark默认为0表示不是收货地址。
        /// </summary>
        public static void InsertAddress(string userId, string address, string consignee, string province, string city, string zip, string phone, string mobile, string area)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "INSERT INTO [dbo].[Address] ([AddressId] ,[UserId] ,[Address]  ,[Consignee] ,[Province] ,[City] ,[District] ,[Zip] ,[Phone] ,[Mobile] ,[Mark] , [Area])  VALUES  " +
                   " ( newid() ,'" + userId + "' ,'" + address + "' ,'" + consignee + "' ,'" + province + "' ,'" + city + "' ,'' ,'" + zip + "' ,'" + phone + "' ,'" + mobile + "' ,0,'" + area + "')";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 向地址表里插入一行信息
        /// </summary>
        public void Insert(Address  aUser)
        {
            m_CosmeticDC.Address .InsertOnSubmit(aUser );
            m_CosmeticDC.SubmitChanges();
        }
        public Address GetAddressById(Guid anAddressId)
        {
            return m_CosmeticDC.Address.FirstOrDefault(theAddress => theAddress.AddressId == anAddressId);
        }

        public List<Address> GetAddressByUser(Guid aUserId)
        {
            return (from theAddress in m_CosmeticDC.Address
                    where theAddress.UserId == aUserId
                    select theAddress).ToList();
        }
        public List<Address> GetAddress(Guid? aUserId)
        {
            return (from theAddress in m_CosmeticDC.Address
                    where theAddress .UserId ==aUserId  && theAddress .Mark ==true  
                    select theAddress).ToList();
        }


        public Address LoginValidate(Guid? aUserId)
        {
            List<Address> theUserList = (from theUser in m_CosmeticDC.Address 
                                      where theUser.UserId  == aUserId  && theUser.Mark  == true 
                                      select theUser).ToList();
            if (theUserList.Count >= 1)
            {
                return theUserList[0];
            }
            return null;
        }


        public void Delete(Address  aUser)
        {
            m_CosmeticDC.Address.DeleteOnSubmit(aUser);
            m_CosmeticDC.SubmitChanges();
        }


        /// <summary>
        /// 查寻出该会员所有的收货地址。
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static DataSet SelectByUserId(string userId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string strCmd = "select * from [Address] where [UserId] = '" + userId + "'";
                SqlCommand com = new SqlCommand(strCmd, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查寻出该会员一条收货地址。
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static DataSet SelectByAddressId(string addressId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string strCmd = "select * from [Address] where [AddressId] = '" + addressId + "'";
                SqlCommand com = new SqlCommand(strCmd, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 从ZIP得到地名。
        /// </summary>
        /// <param name="zipCode"></param>
        /// <returns></returns>
        public static string GetCityName(string zipCode)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string strCmd = "select CityName from dbo.ChinaCity where CityPostCode='" + zipCode + "'";
                SqlCommand com = new SqlCommand(strCmd, con);
                return com.ExecuteScalar().ToString();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 从ZIP得到地名。p代表省，c代表市，a代表地区
        /// </summary>
        /// <param name="zipCode"></param>
        /// <returns></returns>
        public static string GetCityName(string zipCode, string pca)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string strCmd = string.Empty;
                switch (pca)
                {
                    case "p": { strCmd = "select province from dbo.provinces where provinceid='" + zipCode + "'"; }; break;
                    case "c": { strCmd = "select city from dbo.cities where cityid='" + zipCode + "'"; }; break;
                    case "a": { strCmd = "select area from areas where areaid='" + zipCode + "'"; }; break;
                    default: { strCmd = "select province from dbo.provinces where provinceid='" + zipCode + "'"; };
                        break;
                }
                SqlCommand com = new SqlCommand(strCmd, con);
                return com.ExecuteScalar().ToString();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查寻出该会员所有的收货地址。
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static void UpdateByAddressId(string addressId, string address, string consignee, string province, string city, string zip, string phone, string mobile, string area)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string strCmd = "UPDATE [dbo].[Address]  SET [Address] = '" + address + "' ,[Consignee] = '" + consignee + "' ,[Province] = '" + province + "' ,[City] = '" + city + "' ,[Zip] = '" + zip + "'  ,[Phone] = '" + phone + "' ,[Mobile] = '" + mobile + "' , [Area]='" + area + "'  WHERE AddressId = '" + addressId + "'";
                SqlCommand com = new SqlCommand(strCmd, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 删除收货地址。
        /// </summary>
        /// <param name="addressId"></param>
        public static void DeleteByAddressId(string addressId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string strCmd = "DELETE FROM [dbo].[Address] WHERE AddressId = '" + addressId + "'";
                SqlCommand com = new SqlCommand(strCmd, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

    }//end class
}